package batch.makeFile;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.ObjectUtils;
import org.apache.log4j.*;

import batch.common.db.ConnectionManager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.io.*;
import java.util.Date;

public class makeUserFileForZaf
{
    final String TODAY = DateFormatUtils.format(new Date(), "yyyyMMdd");

    public makeUserFileForZaf() {
        ;
    }

    public static void main(String [] args) {
        Logger.getLogger("makeFile.makeUserFileForZaf").info("( * ) Start.");

        makeUserFileForZaf mkRus = new makeUserFileForZaf();
        mkRus.startMake();

        Logger.getLogger("makeFile.makeUserFileForZaf").info("( * ) End.");
    }

    //Make a log file and a data file.
    public void startMake(){
        Connection   conn = null;
        Statement    stmt = null;
        ResultSet    rs   = null;
        ResultSetMetaData  rsmd = null;
        OutputStreamWriter oswFile = null;

        String fileNm  = "/home01/sfc/batch/makeFile/data/zaf_"  + TODAY;

        int cnt = 0;
        int rsCnt = 0;

        StringBuffer sbTemp = new StringBuffer();

        StringBuffer sQuery = new StringBuffer()
        							.append("SELECT A.USER_NO																			 \n")
									.append("     , A.ID                                                                                 \n")
									.append("     , A.PASSWORD_NEW                                                                       \n")
									.append("     , A.FIRSTNAME                                                                          \n")
									.append("     , A.LASTNAME                                                                           \n")
									.append("     , A.GENDER                                                                             \n")
									.append("     , A.BIRTH                                                                              \n")
									.append("     , A.U_EMAIL                                                                            \n")
									.append("     , A.HP_BRAND                                                                           \n")
									.append("     , A.HP_MODEL                                                                           \n")
									.append("     , A.HP_NUM                                                                             \n")
									.append("     , A.HP_PROVIDER                                                                        \n")
									.append("     , A.HP_BUY_YM                                                                          \n")
									.append("     , MAX(DECODE(CODE, '0113001', 'Y', 'N')) NEWSLETTER                                    \n")
									.append("     , MAX(DECODE(CODE, '0113002', 'Y', 'N')) SMS                                           \n")
									.append("     , MAX(DECODE(CODE, '0113003', 'Y', 'N')) PRODUCTNEWSLETTER                             \n")
									.append("     , TO_CHAR(A.CREATED, 'YYYYMMDDHH24MISS')                                               \n")
									.append("     , DECODE(SUBSTR(A.MEMBERGBN, 1, 5), '05314', 'WAP', 'WEB') \"REGISTRATION METHOD\"     \n")
									.append("  FROM G_MB_MEMBER A                                                                     	 \n")
									.append("     , G_MB_OPTIN B                                                                         \n")
									.append("WHERE A.USER_NO = B.USER_NO(+)                                                         	 \n")
									.append("     AND A.REG_COUNTRY = 'zaf'                                                              \n")
									.append("     AND A.DELETED IS NULL                                                                  \n")
									.append("     GROUP BY A.USER_NO                                                                     \n")
									.append("     , A.ID                                                                                 \n")
									.append("     , A.PASSWORD_NEW                                                                       \n")
									.append("     , A.FIRSTNAME                                                                          \n")
									.append("     , A.LASTNAME                                                                           \n")
									.append("     , A.GENDER                                                                             \n")
									.append("     , A.BIRTH                                                                              \n")
									.append("     , A.U_EMAIL                                                                            \n")
									.append("     , A.HP_BRAND                                                                           \n")
									.append("     , A.HP_MODEL                                                                           \n")
									.append("     , A.HP_NUM                                                                             \n")
									.append("     , A.HP_PROVIDER                                                                        \n")
									.append("     , A.HP_BUY_YM                                                                          \n")
									.append("     , A.CREATED                                                                            \n")
									.append("     , A.MEMBERGBN                                                                          \n")      ;


        try {
        	Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@eu-anydb:1521:ORAEU", "gsms", "any02gs");
            //conn = ConnectionManager.getConnection();
            conn.setAutoCommit(true);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sQuery.toString());

            oswFile = new OutputStreamWriter(new FileOutputStream(fileNm, false), "ISO-8859-1");

            while(rs.next()){
                rsmd = rs.getMetaData();
                rsCnt = rsmd.getColumnCount();

                sbTemp.setLength(0);

                for(int i=1;i<=rsCnt;i++) {
                    sbTemp.append(ObjectUtils.toString(rs.getObject(i), ""));

                    if( i == rsCnt ){
                        sbTemp.append("\n");
                    }
                    else{
                        sbTemp.append("|");
                    }
                }

                oswFile.write(sbTemp.toString());
                cnt++;

                if(cnt % 500 == 0){
                    Logger.getLogger("makeFile.makeUserFileForZaf").info("here we are == " + cnt);
                }
            }

            Logger.getLogger("makeFile.makeUserFileForZaf").info("Total Count : " + cnt);
        }
        catch(Exception ex) {
            ex.printStackTrace();

            Logger.getLogger("makeFile.makeUserFileForZaf").info(ex.toString());
            StackTraceElement[] stElement = ex.getStackTrace();
            for(int p=0; p<stElement.length;p++){
                Logger.getLogger("makeFile.makeUserFileForZaf").info(stElement[p].toString());
            }
        }
        finally {
            if (rs   != null) try { rs.close(); }   catch (Exception e) { e.printStackTrace(); }
            if (stmt != null) try { stmt.close(); } catch (Exception e) { e.printStackTrace(); }
            if (conn != null) try { conn.close(); } catch (Exception e) { e.printStackTrace(); }
            if (oswFile != null) try { oswFile.close(); } catch (Exception e) { e.printStackTrace(); }
        }
    }
}